﻿--use PolePosition
GO

-- map html 
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS 
	WHERE TABLE_NAME = 'tb_Utility_Asset_XMissionStructures' AND COLUMN_NAME = 'MapHTML')
ALTER TABLE tb_Utility_Asset_XMissionStructures ADD
	MapHTML varchar(MAX) NULL

GO

/****** Object:  Index [PK_tb_CentralOffices]    Script Date: 02/08/2009 14:58:29 ******/
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[tb_CentralOffices]') AND name = N'PK_tb_CentralOffices')
	ALTER TABLE [dbo].[tb_CentralOffices] DROP CONSTRAINT [PK_tb_CentralOffices]
GO
/****** Object:  Index [PK_tb_CentralOffices]    Script Date: 02/08/2009 14:58:49 ******/
ALTER TABLE [dbo].[tb_CentralOffices] ADD  CONSTRAINT [PK_tb_CentralOffices] PRIMARY KEY CLUSTERED 
(
	[CentralOfficeId] ASC
)WITH (PAD_INDEX  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
GO


IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tb_Vendor_Projects' AND COLUMN_NAME = 'UtilityFk')
	ALTER TABLE tb_Vendor_Projects
		ADD [UtilityFk] int NOT NULL DEFAULT(2)
GO

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tb_Vendor_Projects' AND COLUMN_NAME = 'ProjectTypeFk')
	ALTER TABLE tb_Vendor_Projects
		ADD [ProjectTypeFk] int NULL
GO

--UPDATE tb_Vendor_Projects SET ProjectTypeFk = 1 WHERE Vendor_ProjectId = 6
--UPDATE tb_Vendor_Projects SET ProjectTypeFk = 2 WHERE Vendor_ProjectId = 8
--UPDATE tb_Vendor_Projects SET ProjectTypeFk = 3 WHERE Vendor_ProjectId = 9
--UPDATE tb_Vendor_Projects SET ProjectTypeFk = 1, UtilityFk = 3 WHERE Vendor_ProjectId = 12
--GO



IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tb_Vendor_Contacts' AND COLUMN_NAME = 'Password')
	ALTER TABLE tb_Vendor_Contacts
		ADD [Password] varchar(50) NULL 
GO

--UPDATE tb_Vendor_Contacts SET Password = 'password'
--GO

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tb_Vendor_Contacts' AND COLUMN_NAME = 'Enabled')
	ALTER TABLE tb_Vendor_Contacts
		ADD [Enabled] bit NOT NULL DEFAULT(1)
GO


IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tb_Utility_Contacts' AND COLUMN_NAME = 'Password')
	ALTER TABLE tb_Utility_Contacts
		ADD [Password] varchar(50) NULL 
GO

--UPDATE tb_Utility_Contacts SET Password = 'password'
--GO

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tb_Utility_Contacts' AND COLUMN_NAME = 'Enabled')
	ALTER TABLE tb_Utility_Contacts
		ADD [Enabled] bit NOT NULL DEFAULT(1)
GO

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tb_CentralOffices' AND COLUMN_NAME = 'UtilityFk')
	ALTER TABLE tb_CentralOffices
		ADD [UtilityFk] int NULL
GO

--UPDATE tb_CentralOffices SET UtilityFk = 2 
--GO

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tb_Utility_Polygons' AND COLUMN_NAME = 'UtilityFk')
	ALTER TABLE tb_Utility_Polygons
		ADD [UtilityFk] int NOT NULL CONSTRAINT [UtilityFk_Default] DEFAULT(2)
GO

--ALTER TABLE [dbo].[tb_Utility_Polygons] DROP CONSTRAINT [UtilityFk_Default]
--GO

--FIX BAD DATA
UPDATE tb_Vendor_Visit_XMissionStructures_Poles
SET Length = NULL
WHERE Length = -1.7976931348623157E+308
GO


IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tb_Utility_Asset_Distributions' AND COLUMN_NAME = 'MapFk')
	ALTER TABLE tb_Utility_Asset_Distributions
		ADD [MapFk] int NULL
GO

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tb_Utility_Asset_Distributions' AND COLUMN_NAME = 'SectionFk')
	ALTER TABLE tb_Utility_Asset_Distributions
		ADD [SectionFk] int NULL
GO

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tb_Utility_Asset_Meters' AND COLUMN_NAME = 'MapFk')
	ALTER TABLE tb_Utility_Asset_Meters
		ADD [MapFk] int NULL
GO

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tb_Utility_Asset_Meters' AND COLUMN_NAME = 'SectionFk')
	ALTER TABLE tb_Utility_Asset_Meters
		ADD [SectionFk] int NULL
GO

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tb_Utility_Asset_XMissionStructures_Poles' AND COLUMN_NAME = 'MapFk')
	ALTER TABLE tb_Utility_Asset_XMissionStructures_Poles
		ADD [MapFk] int NULL
GO

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tb_Utility_Asset_XMissionStructures_Poles' AND COLUMN_NAME = 'SectionFk')
	ALTER TABLE tb_Utility_Asset_XMissionStructures_Poles
		ADD [SectionFk] int NULL
GO

--UPDATE tb_Utility_Asset_XMissionStructures_Poles
--SET
-- 	 MapFk = Visit.MapFk
-- 	,SectionFk = Visit.SectionFk
--FROM tb_Utility_Asset_XMissionStructures_Poles Asset
--INNER JOIN tb_Vendor_InspVisit_XmissionStructures_Poles Visit
--	ON Visit.Utility_Asset_XMissionStructures_PoleFk = Asset.Utility_Asset_XMissionStructures_PoleId

GO

UPDATE tb_Utility_Asset_XMissionStructures
SET UtilityFk = 2
WHERE UtilityFk IS NULL

GO

--FIX BAD DATA
UPDATE tb_Utility_Asset_XMissionStructures_Poles
SET Length = NULL
WHERE Length = -1.7976931348623157E+308
GO




IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tb_Utility_Asset_Distributions' AND COLUMN_NAME = 'Year')
	ALTER TABLE tb_Utility_Asset_Distributions
		ADD [Year] int NULL
GO

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tb_Utility_Asset_Distributions' AND COLUMN_NAME = 'TreatmentFk')
	ALTER TABLE tb_Utility_Asset_Distributions
		ADD [TreatmentFk] int NULL
GO

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tb_Utility_Asset_Distributions' AND COLUMN_NAME = 'ClassFk')
	ALTER TABLE tb_Utility_Asset_Distributions
		ADD [ClassFk] int NULL
GO

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tb_Utility_Asset_Distributions' AND COLUMN_NAME = 'SpeciesFk')
	ALTER TABLE tb_Utility_Asset_Distributions
		ADD [SpeciesFk] int NULL
GO

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tb_Utility_Asset_Distributions' AND COLUMN_NAME = 'Length')
	ALTER TABLE tb_Utility_Asset_Distributions
		ADD [Length] int NULL
GO


IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tb_Utility_Asset_Meters' AND COLUMN_NAME = 'Number')
	ALTER TABLE tb_Utility_Asset_Meters
		ADD [Number] varchar(25) NULL
GO

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tb_Utility_Asset_Meters' AND COLUMN_NAME = 'Comments')
	ALTER TABLE tb_Utility_Asset_Meters
		ADD [Comments] ntext NULL
GO

IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tb_Utility_Asset_Distribution_PhasesXPole_tb_Distribution_Phases]') AND parent_object_id = OBJECT_ID(N'[dbo].[tb_Utility_Asset_Distribution_PhasesXPole]'))
	ALTER TABLE [dbo].[tb_Utility_Asset_Distribution_PhasesXPole] DROP CONSTRAINT [FK_tb_Utility_Asset_Distribution_PhasesXPole_tb_Distribution_Phases]
GO
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tb_Utility_Asset_Distribution_PhasesXPole_tb_Utility_Asset_Distributions]') AND parent_object_id = OBJECT_ID(N'[dbo].[tb_Utility_Asset_Distribution_PhasesXPole]'))
	ALTER TABLE [dbo].[tb_Utility_Asset_Distribution_PhasesXPole] DROP CONSTRAINT [FK_tb_Utility_Asset_Distribution_PhasesXPole_tb_Utility_Asset_Distributions]
GO
/****** Object:  Table [dbo].[tb_Utility_Asset_Distribution_PhasesXPole]    Script Date: 03/05/2009 14:59:43 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tb_Utility_Asset_Distribution_PhasesXPole]') AND type in (N'U'))
	DROP TABLE [dbo].[tb_Utility_Asset_Distribution_PhasesXPole]
GO
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tb_Utility_Asset_Distribution_TopsXPole_tb_Distributions_TopTypes]') AND parent_object_id = OBJECT_ID(N'[dbo].[tb_Utility_Asset_Distribution_TopsXPole]'))
	ALTER TABLE [dbo].[tb_Utility_Asset_Distribution_TopsXPole] DROP CONSTRAINT [FK_tb_Utility_Asset_Distribution_TopsXPole_tb_Distributions_TopTypes]
GO
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tb_Utility_Asset_Distribution_TopsXPole_tb_Utility_Asset_Distributions]') AND parent_object_id = OBJECT_ID(N'[dbo].[tb_Utility_Asset_Distribution_TopsXPole]'))
	ALTER TABLE [dbo].[tb_Utility_Asset_Distribution_TopsXPole] DROP CONSTRAINT [FK_tb_Utility_Asset_Distribution_TopsXPole_tb_Utility_Asset_Distributions]
GO
/****** Object:  Table [dbo].[tb_Utility_Asset_Distribution_TopsXPole]    Script Date: 03/05/2009 15:00:16 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tb_Utility_Asset_Distribution_TopsXPole]') AND type in (N'U'))
	DROP TABLE [dbo].[tb_Utility_Asset_Distribution_TopsXPole]
GO
/****** Object:  Table [dbo].[tb_Utility_Asset_Distribution_PhasesXPole]    Script Date: 03/05/2009 15:00:36 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tb_Utility_Asset_Distribution_PhasesXPole](
	[DistributionPhasePoleId] [int] IDENTITY(1,1) NOT NULL,
	[Utility_Asset_DistributionFk] [int] NOT NULL,
	[PhaseFk] [int] NOT NULL,
 CONSTRAINT [PK_tb_Utility_Asset_Distribution_PhasesXPole] PRIMARY KEY CLUSTERED 
(
	[DistributionPhasePoleId] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[tb_Utility_Asset_Distribution_PhasesXPole]  WITH CHECK ADD  CONSTRAINT [FK_tb_Utility_Asset_Distribution_PhasesXPole_tb_Distribution_Phases] FOREIGN KEY([PhaseFk])
REFERENCES [dbo].[tb_Distribution_Phases] ([PhaseId])
GO
ALTER TABLE [dbo].[tb_Utility_Asset_Distribution_PhasesXPole] CHECK CONSTRAINT [FK_tb_Utility_Asset_Distribution_PhasesXPole_tb_Distribution_Phases]
GO
ALTER TABLE [dbo].[tb_Utility_Asset_Distribution_PhasesXPole]  WITH CHECK ADD  CONSTRAINT [FK_tb_Utility_Asset_Distribution_PhasesXPole_tb_Utility_Asset_Distributions] FOREIGN KEY([Utility_Asset_DistributionFk])
REFERENCES [dbo].[tb_Utility_Asset_Distributions] ([Utility_Asset_DistributionId])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[tb_Utility_Asset_Distribution_PhasesXPole] CHECK CONSTRAINT [FK_tb_Utility_Asset_Distribution_PhasesXPole_tb_Utility_Asset_Distributions]
GO
/****** Object:  Table [dbo].[tb_Utility_Asset_Distribution_TopsXPole]    Script Date: 03/05/2009 15:00:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tb_Utility_Asset_Distribution_TopsXPole](
	[DistributionTopTypePoleId] [int] IDENTITY(1,1) NOT NULL,
	[Utility_Asset_DistributionFk] [int] NOT NULL,
	[TopTypeFk] [int] NOT NULL,
 CONSTRAINT [PK_tb_Utility_Asset_Distribution_TopsXPole] PRIMARY KEY CLUSTERED 
(
	[DistributionTopTypePoleId] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[tb_Utility_Asset_Distribution_TopsXPole]  WITH CHECK ADD  CONSTRAINT [FK_tb_Utility_Asset_Distribution_TopsXPole_tb_Distributions_TopTypes] FOREIGN KEY([TopTypeFk])
REFERENCES [dbo].[tb_Distributions_TopTypes] ([Distributions_TopTypeId])
GO
ALTER TABLE [dbo].[tb_Utility_Asset_Distribution_TopsXPole] CHECK CONSTRAINT [FK_tb_Utility_Asset_Distribution_TopsXPole_tb_Distributions_TopTypes]
GO
ALTER TABLE [dbo].[tb_Utility_Asset_Distribution_TopsXPole]  WITH CHECK ADD  CONSTRAINT [FK_tb_Utility_Asset_Distribution_TopsXPole_tb_Utility_Asset_Distributions] FOREIGN KEY([Utility_Asset_DistributionFk])
REFERENCES [dbo].[tb_Utility_Asset_Distributions] ([Utility_Asset_DistributionId])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[tb_Utility_Asset_Distribution_TopsXPole] CHECK CONSTRAINT [FK_tb_Utility_Asset_Distribution_TopsXPole_tb_Utility_Asset_Distributions]
GO



IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tb_Utility_Settings' AND COLUMN_NAME = 'SettingName')
	ALTER TABLE tb_Utility_Settings
		ADD [SettingName] varchar(50) NULL
GO

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tb_Utility_Settings' AND COLUMN_NAME = 'SettingValue')
	ALTER TABLE tb_Utility_Settings
		ADD [SettingValue] varchar(255) NULL
GO


INSERT tb_Utility_Settings
(
	 UtilityFk
	,SettingName
	,SettingValue
)
SELECT 
	 UtilityId
	,'WorkQueueResultTypes'
	,'2,3,4,5,6'
FROM tb_Utilities
LEFT JOIN tb_Utility_Settings
	ON UtilityId = UtilityFk
	AND SettingName = 'WorkQueueResultTypes'
WHERE UtilityFk IS NULL

INSERT tb_Utility_Settings
(
	 UtilityFk
	,SettingName
	,SettingValue
)
SELECT 
	 UtilityId
	,'WorkQueueIncludeElectrical'
	,'1'
FROM tb_Utilities
LEFT JOIN tb_Utility_Settings
	ON UtilityId = UtilityFk
	AND SettingName = 'WorkQueueIncludeElectrical'
WHERE UtilityFk IS NULL
GO

/****** Object:  Index [idx_tb_Vendor_InspVisit_XmissionStructures_Results_PoleFk]    Script Date: 03/27/2009 13:24:29 ******/
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[tb_Vendor_InspVisit_XmissionStructures_Results]') AND name = N'idx_tb_Vendor_InspVisit_XmissionStructures_Results_PoleFk')
	DROP INDEX [idx_tb_Vendor_InspVisit_XmissionStructures_Results_PoleFk] ON [dbo].[tb_Vendor_InspVisit_XmissionStructures_Results] WITH ( ONLINE = OFF )

GO
/****** Object:  Index [idx_PoleFk]    Script Date: 03/27/2009 13:23:44 ******/
CREATE NONCLUSTERED INDEX [idx_tb_Vendor_InspVisit_XmissionStructures_Results_PoleFk] ON [dbo].[tb_Vendor_InspVisit_XmissionStructures_Results] 
(
	[Vendor_InspVisit_XMissionStructure_PoleFk] ASC
)WITH (PAD_INDEX  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
GO


/****** Object:  Table [dbo].[tb_Vendor_Distribution_Pole_Remedy]    Script Date: 03/29/2009 10:45:44 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tb_Vendor_Distribution_Pole_Remedy]') AND type in (N'U'))
	DROP TABLE [dbo].[tb_Vendor_Distribution_Pole_Remedy]

/****** Object:  Table [dbo].[tb_Vendor_Distribution_Pole_Remedy]    Script Date: 03/29/2009 10:40:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tb_Vendor_Distribution_Pole_Remedy](
	[RemedyId] [int] IDENTITY(1,1) NOT NULL,
	[PoleFk] [int] NULL,
	[RemedyStatusFK] [int] NULL,
	[RemedyAssigned] [nvarchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[RemedyResolvedDate] [datetime] NULL,
	[RemedyNotes] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[RemedyLastAction] [datetime] NULL,
 CONSTRAINT [PK_tb_Vendor_Distribution_Pole_Remedy] PRIMARY KEY CLUSTERED 
(
	[RemedyId] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO


IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tb_Utility_Cross_Vendor_tb_Utilities]') AND parent_object_id = OBJECT_ID(N'[dbo].[tb_Utility_Cross_Vendor]'))
	ALTER TABLE [dbo].[tb_Utility_Cross_Vendor] DROP CONSTRAINT [FK_tb_Utility_Cross_Vendor_tb_Utilities]
GO
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tb_Utility_Cross_Vendor_tb_Vendors]') AND parent_object_id = OBJECT_ID(N'[dbo].[tb_Utility_Cross_Vendor]'))
	ALTER TABLE [dbo].[tb_Utility_Cross_Vendor] DROP CONSTRAINT [FK_tb_Utility_Cross_Vendor_tb_Vendors]
GO
/****** Object:  Table [dbo].[tb_Utility_Cross_Vendor]    Script Date: 03/29/2009 10:48:26 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tb_Utility_Cross_Vendor]') AND type in (N'U'))
	DROP TABLE [dbo].[tb_Utility_Cross_Vendor]
GO	

/****** Object:  Table [dbo].[tb_Utility_Cross_Vendor]    Script Date: 03/29/2009 10:49:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tb_Utility_Cross_Vendor](
	[CrossId] [int] IDENTITY(1,1) NOT NULL,
	[VendorFk] [int] NOT NULL,
	[UtilityFk] [int] NOT NULL,
 CONSTRAINT [PK_tb_Utility_Cross_Vendor] PRIMARY KEY CLUSTERED 
(
	[CrossId] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[tb_Utility_Cross_Vendor]  WITH CHECK ADD  CONSTRAINT [FK_tb_Utility_Cross_Vendor_tb_Utilities] FOREIGN KEY([UtilityFk])
REFERENCES [dbo].[tb_Utilities] ([UtilityId])
GO
ALTER TABLE [dbo].[tb_Utility_Cross_Vendor] CHECK CONSTRAINT [FK_tb_Utility_Cross_Vendor_tb_Utilities]
GO
ALTER TABLE [dbo].[tb_Utility_Cross_Vendor]  WITH CHECK ADD  CONSTRAINT [FK_tb_Utility_Cross_Vendor_tb_Vendors] FOREIGN KEY([VendorFk])
REFERENCES [dbo].[tb_Vendors] ([VendorId])
GO
ALTER TABLE [dbo].[tb_Utility_Cross_Vendor] CHECK CONSTRAINT [FK_tb_Utility_Cross_Vendor_tb_Vendors]
GO

INSERT tb_Utility_Cross_Vendor (UtilityFk, VendorFk)
VALUES (2, 3)

INSERT tb_Utility_Cross_Vendor (UtilityFk, VendorFk)
VALUES (2, 4)
GO


IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tb_Vendor_InspVisit_XmissionStructures_Poles_tb_Vendor_Contacts]') AND parent_object_id = OBJECT_ID(N'[dbo].[tb_Vendor_InspVisit_XmissionStructures_Poles]'))
	ALTER TABLE [dbo].[tb_Vendor_InspVisit_XmissionStructures_Poles] DROP CONSTRAINT [FK_tb_Vendor_InspVisit_XmissionStructures_Poles_tb_Vendor_Contacts]
GO

ALTER TABLE [dbo].[tb_Vendor_InspVisit_XmissionStructures_Poles]  WITH CHECK ADD  CONSTRAINT [FK_tb_Vendor_InspVisit_XmissionStructures_Poles_tb_Vendor_Contacts] FOREIGN KEY([Vendor_ContactFK])
REFERENCES [dbo].[tb_Vendor_Contacts] ([Vendor_ContactId])
GO
ALTER TABLE [dbo].[tb_Vendor_InspVisit_XmissionStructures_Poles] CHECK CONSTRAINT [FK_tb_Vendor_InspVisit_XmissionStructures_Poles_tb_Vendor_Contacts]
GO


--FIX Distribution Inspections to be vendor contacts not utility contacts 
-- UPDATE tb_Vendor_Distribution_Poles
-- SET InspectorFk = 7
-- WHERE InspectorFk = 6

-- UPDATE tb_Vendor_Distribution_Poles
-- SET InspectorFk = 8
-- WHERE InspectorFk = 5

-- UPDATE tb_Vendor_Distribution_Poles
-- SET InspectorFk = 9
-- WHERE InspectorFk = 4

-- UPDATE tb_Vendor_Distribution_Poles
-- SET InspectorFk = 19
-- WHERE InspectorFk = 3

GO
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tb_Vendor_Distribution_Poles_tb_Vendor_Contacts]') AND parent_object_id = OBJECT_ID(N'[dbo].[tb_Vendor_Distribution_Poles]'))
	ALTER TABLE [dbo].[tb_Vendor_Distribution_Poles] DROP CONSTRAINT [FK_tb_Vendor_Distribution_Poles_tb_Vendor_Contacts]
GO

ALTER TABLE [dbo].[tb_Vendor_Distribution_Poles]  WITH CHECK ADD  CONSTRAINT [FK_tb_Vendor_Distribution_Poles_tb_Vendor_Contacts] FOREIGN KEY([InspectorFk])
REFERENCES [dbo].[tb_Vendor_Contacts] ([Vendor_ContactId])
GO
ALTER TABLE [dbo].[tb_Vendor_Distribution_Poles] CHECK CONSTRAINT [FK_tb_Vendor_Distribution_Poles_tb_Vendor_Contacts]
GO

/****** Object:  Table [dbo].[tb_Reports_Project]    Script Date: 04/03/2009 10:57:11 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tb_Reports_Project]') AND type in (N'U'))
	DROP TABLE [dbo].[tb_Reports_Project]
GO


/****** Object:  Table [dbo].[tb_Reports_Project]    Script Date: 04/03/2009 10:58:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tb_Reports_Project](
	[ReportId] [int] IDENTITY(1,1) NOT NULL,
	[ReportFileName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[ReportDisplayName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[ReportDescription] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[ProjectTypeFk] [tinyint] NULL,
	[Active] [bit] NOT NULL CONSTRAINT [DF_tb_Reports_Project_Active]  DEFAULT ((1)),
 CONSTRAINT [PK_tb_Reports_Project] PRIMARY KEY CLUSTERED 
(
	[ReportId] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO

/****** Object:  Table [dbo].[tb_Project_Types]    Script Date: 04/03/2009 11:00:25 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tb_Project_Types]') AND type in (N'U'))
	DROP TABLE [dbo].[tb_Project_Types]
GO

/****** Object:  Table [dbo].[tb_Project_Types]    Script Date: 04/03/2009 11:00:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tb_Project_Types](
	[ProjectTypeId] [tinyint] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](50) NOT NULL,
 CONSTRAINT [PK_tb_Project_Types] PRIMARY KEY CLUSTERED 
(
	[ProjectTypeId] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO


SET XACT_ABORT ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF

BEGIN TRANSACTION

-- --Table: [dbo].[tb_Project_Types]
SET IDENTITY_INSERT [dbo].[tb_Project_Types] ON
-- --Insert
INSERT INTO [dbo].[tb_Project_Types] ([ProjectTypeId], [Name]) VALUES(1, 'Transmission Inspection')
INSERT INTO [dbo].[tb_Project_Types] ([ProjectTypeId], [Name]) VALUES(2, 'Transmission Gps Visit')
INSERT INTO [dbo].[tb_Project_Types] ([ProjectTypeId], [Name]) VALUES(3, 'Distribution Inspection')
INSERT INTO [dbo].[tb_Project_Types] ([ProjectTypeId], [Name]) VALUES(4, 'Distribution Gps Visit')

SET IDENTITY_INSERT [dbo].[tb_Project_Types] OFF

COMMIT TRANSACTION
GO


/****** Object:  Index [PK_tb_Settings]    Script Date: 02/08/2009 14:58:29 ******/
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[tb_Settings]') AND name = N'PK_tb_Settings')
	ALTER TABLE [dbo].[tb_Settings] DROP CONSTRAINT [PK_tb_Settings]
GO
/****** Object:  Index [PK_tb_Settings]    Script Date: 02/08/2009 14:58:49 ******/
ALTER TABLE [dbo].[tb_Settings] ADD  CONSTRAINT [PK_tb_Settings] PRIMARY KEY CLUSTERED 
(
	[SettingId] ASC
)WITH (PAD_INDEX  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
GO


BEGIN TRANSACTION

-- --Table: [dbo].[tb_Reports_Project]
SET IDENTITY_INSERT [dbo].[tb_Reports_Project] ON
-- --Insert
INSERT INTO [dbo].[tb_Reports_Project] ([ReportId], [ReportFileName], [ReportDisplayName], [ReportDescription], [ProjectTypeFk], [Active]) VALUES(1, 'XmissionInspectionProjectSummary', 'Project Summary', 'Project Summary', 1, 1)
INSERT INTO [dbo].[tb_Reports_Project] ([ReportId], [ReportFileName], [ReportDisplayName], [ReportDescription], [ProjectTypeFk], [Active]) VALUES(2, 'XmissionGpsProjectVisitsByContact', 'Visits by Contact', 'Project Visits by Inspector', 2, 1)
INSERT INTO [dbo].[tb_Reports_Project] ([ReportId], [ReportFileName], [ReportDisplayName], [ReportDescription], [ProjectTypeFk], [Active]) VALUES(3, 'XmissionGpsProjectVisitsByWeek', 'Visits by Week', 'Project Visits by Week', 2, 1)
INSERT INTO [dbo].[tb_Reports_Project] ([ReportId], [ReportFileName], [ReportDisplayName], [ReportDescription], [ProjectTypeFk], [Active]) VALUES(4, 'XmissionInspectionProjectByWeekAndInspector', 'Inspections by Week', 'Inspections by Week', 1, 1)
INSERT INTO [dbo].[tb_Reports_Project] ([ReportId], [ReportFileName], [ReportDisplayName], [ReportDescription], [ProjectTypeFk], [Active]) VALUES(5, 'XmissionInspectionProjectByInspector', 'Inspections by Inspector', 'Inspections by Inspector', 1, 1)
INSERT INTO [dbo].[tb_Reports_Project] ([ReportId], [ReportFileName], [ReportDisplayName], [ReportDescription], [ProjectTypeFk], [Active]) VALUES(6, 'XmissionInspectionProjectIssueByResult', 'Inspections by Result', 'Inspections by Result', 1, 1)
INSERT INTO [dbo].[tb_Reports_Project] ([ReportId], [ReportFileName], [ReportDisplayName], [ReportDescription], [ProjectTypeFk], [Active]) VALUES(7, 'XmissionInspectionProjectSumarizeLineProblems', 'Inspection Issues', 'Inspection Issues', 1, 1)
INSERT INTO [dbo].[tb_Reports_Project] ([ReportId], [ReportFileName], [ReportDisplayName], [ReportDescription], [ProjectTypeFk], [Active]) VALUES(8, 'DistributionInspectionTypeByWeekAndInspector', 'Inspections by Week', 'Inspections by Week', 3, 1)
INSERT INTO [dbo].[tb_Reports_Project] ([ReportId], [ReportFileName], [ReportDisplayName], [ReportDescription], [ProjectTypeFk], [Active]) VALUES(9, 'DistributionInspectionProjectSummary', 'Project Summary', 'Project Summary', 3, 1)
INSERT INTO [dbo].[tb_Reports_Project] ([ReportId], [ReportFileName], [ReportDisplayName], [ReportDescription], [ProjectTypeFk], [Active]) VALUES(10, 'ProjectPoleTopTypes', 'Visits By Top Type', 'Visits By Top Type', null, 1)
INSERT INTO [dbo].[tb_Reports_Project] ([ReportId], [ReportFileName], [ReportDisplayName], [ReportDescription], [ProjectTypeFk], [Active]) VALUES(11, 'ProjectPoleAges', 'Visits By Pole Age', 'Visits By Pole Age', null, 1)

SET IDENTITY_INSERT [dbo].[tb_Reports_Project] OFF


COMMIT TRANSACTION
GO

BEGIN TRANSACTION

-- --Table: [dbo].[tb_Settings]
SET IDENTITY_INSERT [dbo].[tb_Settings] ON
-- --Insert

IF NOT EXISTS(SELECT SettingId FROM tb_Settings WHERE SettingId = 15)
	INSERT INTO [dbo].[tb_Settings] ([SettingId], [SettingKey], [SettingValue], [SettingText]) VALUES(15, N'ReportServerUrl', N'https://rs2k501.discountasp.net/ReportServer', 'Report Server Url')

IF NOT EXISTS(SELECT SettingId FROM tb_Settings WHERE SettingId = 16)
	INSERT INTO [dbo].[tb_Settings] ([SettingId], [SettingKey], [SettingValue], [SettingText]) VALUES(16, N'ReportFolder', N'/devpoleposi/Reports/Development/', 'Report Server Folder containing the reports')

IF NOT EXISTS(SELECT SettingId FROM tb_Settings WHERE SettingId = 17)
	INSERT INTO [dbo].[tb_Settings] ([SettingId], [SettingKey], [SettingValue], [SettingText]) VALUES(17, N'ReportServerUser', N'PPIApp', 'Report Server User')

IF NOT EXISTS(SELECT SettingId FROM tb_Settings WHERE SettingId = 18)
	INSERT INTO [dbo].[tb_Settings] ([SettingId], [SettingKey], [SettingValue], [SettingText]) VALUES(18, N'ReportServerPassword', N'PPIPassw0rd', 'Report Server Password')

IF NOT EXISTS(SELECT SettingId FROM tb_Settings WHERE SettingId = 19)
	INSERT INTO [dbo].[tb_Settings] ([SettingId], [SettingKey], [SettingValue], [SettingText]) VALUES(19, N'ReportServerDomain', N'RS2K501', 'Report Server Domain')

IF NOT EXISTS(SELECT SettingId FROM tb_Settings WHERE SettingId = 20)
	INSERT INTO [dbo].[tb_Settings] ([SettingId], [SettingKey], [SettingValue], [SettingText]) VALUES(20, N'BaseImageStorageUrl', N'http://www.dev-poleposition.com/BETA/Images/', 'Base Url For Image storage')

IF NOT EXISTS(SELECT SettingId FROM tb_Settings WHERE SettingId = 21)
	INSERT INTO [dbo].[tb_Settings] ([SettingId], [SettingKey], [SettingValue], [SettingText]) VALUES(21, N'ImageViewerUrl', N'http://www.dev-poleposition.com/Dev.v3/WebService/ImageViewer/Image.aspx', 'Image Viewer Url')

IF NOT EXISTS(SELECT SettingId FROM tb_Settings WHERE SettingId = 22)
	INSERT INTO [dbo].[tb_Settings] ([SettingId], [SettingKey], [SettingValue], [SettingText]) VALUES(22, N'DirectionsUrl', N'http://www.dev-poleposition.com/Deploy/WebSite2.1/GEMap.aspx?f=d&hl=en&saddr={0}&daddr={1}&om=1', 'The Url used to provide directions')
	
IF NOT EXISTS(SELECT SettingId FROM tb_Settings WHERE SettingId = 23)
	INSERT INTO [dbo].[tb_Settings] ([SettingId], [SettingKey], [SettingValue], [SettingText]) VALUES(23, N'IFrameSource', N'http://dev-poleposition.com/Dev.v3/WebService/MapServices/', 'IFrame URL for google earth bubbles')	

IF NOT EXISTS(SELECT SettingId FROM tb_Settings WHERE SettingId = 24)
	INSERT INTO [dbo].[tb_Settings] ([SettingId], [SettingKey], [SettingValue], [SettingText]) VALUES(24, N'MapImageUrl', N'http://dev-poleposition.com/Dev.v3/WebService/GetImage.aspx?id={0}&type={1}&height={2}&width={3}', 'Image URL for google earth bubbles')	

IF NOT EXISTS(SELECT SettingId FROM tb_Settings WHERE SettingId = 25)
	INSERT INTO [dbo].[tb_Settings] ([SettingId], [SettingKey], [SettingValue], [SettingText]) VALUES(25, N'FullImageUrl', N'http://dev-poleposition.com/Dev.v3/WebService/GetImage.aspx?id={0}&type={1}&height={2}&width={3}', 'Full image URL for google earth bubbles')	

IF NOT EXISTS(SELECT SettingId FROM tb_Settings WHERE SettingId = 26)
	INSERT INTO [dbo].[tb_Settings] ([SettingId], [SettingKey], [SettingValue], [SettingText]) VALUES(26, N'MapIconUrl', N'http://dev-poleposition.com/Dev/Icons/{0}', 'Google earth icons')	

SET IDENTITY_INSERT [dbo].[tb_Settings] OFF

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tb_Vendor_Distribution_Poles' AND COLUMN_NAME = 'Utility_Asset_Distributions_PoleFk')
	ALTER TABLE tb_Vendor_Distribution_Poles
		ADD [Utility_Asset_Distributions_PoleFk] int NULL
GO


-- New foreign key between -> tb_Utility_Asset_Distributions and tb_Vendor_Distribution_Poles 
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_SCHEMA='dbo' AND CONSTRAINT_NAME='FK_tb_Vendor_Distribution_Poles_tb_Utility_Asset_Distributions' AND TABLE_NAME='tb_Vendor_Distribution_Poles')
BEGIN
  ALTER TABLE tb_Vendor_Distribution_Poles
  DROP CONSTRAINT [FK_tb_Vendor_Distribution_Poles_tb_Utility_Asset_Distributions]
END

ALTER TABLE dbo.tb_Vendor_Distribution_Poles ADD CONSTRAINT
	FK_tb_Vendor_Distribution_Poles_tb_Utility_Asset_Distributions FOREIGN KEY
	(
	Utility_Asset_Distributions_PoleFk
	) REFERENCES dbo.tb_Utility_Asset_Distributions
	(
	Utility_Asset_DistributionId
	) 
GO

--New fields for Inspection Tx Poles
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tb_Vendor_InspVisit_XmissionStructures_Poles' AND COLUMN_NAME = 'Vendor_Visit_TypeFK')
	ALTER TABLE tb_Vendor_InspVisit_XmissionStructures_Poles
		ADD [Vendor_Visit_TypeFK] int NULL

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tb_Vendor_InspVisit_XmissionStructures_Poles' AND COLUMN_NAME = 'GuardsQuantity')
	ALTER TABLE tb_Vendor_InspVisit_XmissionStructures_Poles
		ADD [GuardsQuantity] int NULL


IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tb_Vendor_InspVisit_XmissionStructures_Poles' AND COLUMN_NAME = 'LInspector')
	ALTER TABLE tb_Vendor_InspVisit_XmissionStructures_Poles
		ADD [LInspector] nvarchar(100) NULL
GO


IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tb_Vendor_Distribution_Poles' AND COLUMN_NAME = 'GuardsQuantity')
	ALTER TABLE tb_Vendor_Distribution_Poles ADD
		GuardsQuantity int NULL

GO

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tb_Vendor_Distribution_Poles' AND COLUMN_NAME = 'GuardsQuantity')
	ALTER TABLE tb_Vendor_Distribution_Poles ADD
		GuardsQuantity int NULL

GO

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tb_Vendor_Distribution_Poles' AND COLUMN_NAME = 'PoleTag')
	ALTER TABLE tb_Vendor_Distribution_Poles ADD
		PoleTag bit NULL
GO


IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tb_Vendor_Distribution_Poles' AND COLUMN_NAME = 'GroundWireRepair')
	ALTER TABLE tb_Vendor_Distribution_Poles ADD
		GroundWireRepair bit NULL
GO


IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tb_Vendor_Visit_XMissionStructures_Poles' AND COLUMN_NAME = 'MapFk')
	ALTER TABLE tb_Vendor_Visit_XMissionStructures_Poles ADD
		MapFk int NULL
GO

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tb_Vendor_Visit_XMissionStructures_Poles' AND COLUMN_NAME = 'Section')
	ALTER TABLE tb_Vendor_Visit_XMissionStructures_Poles ADD
		[Section] int NULL
GO

-- tb_Vendor_Projects new fields

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tb_Vendor_Projects' AND COLUMN_NAME = 'EstimatedPartialCount')
	ALTER TABLE tb_Vendor_Projects
		ADD [EstimatedPartialCount] int NULL 

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tb_Vendor_Projects' AND COLUMN_NAME = 'EstimatedVisualCount')
	ALTER TABLE tb_Vendor_Projects
		ADD [EstimatedVisualCount] int NULL 

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tb_Vendor_Projects' AND COLUMN_NAME = 'EstimatedFullCount')
	ALTER TABLE tb_Vendor_Projects
		ADD [EstimatedFullCount] int NULL 

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tb_Vendor_Projects' AND COLUMN_NAME = 'EstimatedGuyGuardsCount')
	ALTER TABLE tb_Vendor_Projects
		ADD [EstimatedGuyGuardsCount] int NULL 

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tb_Vendor_Projects' AND COLUMN_NAME = 'EstimatedGroundWireCount')
	ALTER TABLE tb_Vendor_Projects
		ADD [EstimatedGroundWireCount] int NULL 

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tb_Vendor_Projects' AND COLUMN_NAME = 'EstimatedPoleTagCount')
	ALTER TABLE tb_Vendor_Projects
		ADD [EstimatedPoleTagCount] int NULL 

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tb_Vendor_Projects' AND COLUMN_NAME = 'EstimatedPartialCount')
	ALTER TABLE tb_Vendor_Projects
		ADD [EstimatedPartialCount] int NULL 

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tb_Vendor_Projects' AND COLUMN_NAME = 'PartialUnitPrice')
	ALTER TABLE tb_Vendor_Projects
		ADD [PartialUnitPrice] decimal(19,2) NULL 

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tb_Vendor_Projects' AND COLUMN_NAME = 'VisualUnitPrice')
	ALTER TABLE tb_Vendor_Projects
		ADD [VisualUnitPrice] decimal(19,2) NULL 

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tb_Vendor_Projects' AND COLUMN_NAME = 'FullUnitPrice')
	ALTER TABLE tb_Vendor_Projects
		ADD [FullUnitPrice] decimal(19,2) NULL 

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tb_Vendor_Projects' AND COLUMN_NAME = 'GuyGuardsUnitPrice')
	ALTER TABLE tb_Vendor_Projects
		ADD [GuyGuardsUnitPrice] decimal(19,2) NULL 

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tb_Vendor_Projects' AND COLUMN_NAME = 'GroundWireUnitPrice')
	ALTER TABLE tb_Vendor_Projects
		ADD [GroundWireUnitPrice] decimal(19,2) NULL 

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tb_Vendor_Projects' AND COLUMN_NAME = 'PoleTagUnitPrice')
	ALTER TABLE tb_Vendor_Projects
		ADD [PoleTagUnitPrice] decimal(19,2) NULL 

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tb_Utility_Asset_Distributions' AND COLUMN_NAME = 'Number')
ALTER TABLE tb_Utility_Asset_Distributions
	ADD Number varchar(256)

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tb_Utility_Asset_Distributions' AND COLUMN_NAME = 'CreatedOn')
ALTER TABLE tb_Utility_Asset_Distributions
	ADD CreatedOn datetime default getdate()

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tb_Utility_Asset_Distributions' AND COLUMN_NAME = 'CreatedBy')
ALTER TABLE tb_Utility_Asset_Distributions
	ADD CreatedBy varchar(255) not null

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tb_Utility_Asset_Distributions' AND COLUMN_NAME = 'UtilityKey')
ALTER TABLE tb_Utility_Asset_Distributions
	ADD UtilityKey varchar(255) 

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tb_Utility_Asset_Distributions' AND COLUMN_NAME = 'UtilityGroup')
ALTER TABLE tb_Utility_Asset_Distributions
	ADD UtilityGroup varchar(255) 

--Add column to save the old top type style 
IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tb_Distributions_TopTypes' AND COLUMN_NAME = 'OldStyleName')
ALTER TABLE tb_Distributions_TopTypes
	ADD OldStyleName nvarchar(50) NULL


IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tb_Vendor_Distribution_Poles' AND COLUMN_NAME = 'ConductorType')
	ALTER TABLE tb_Vendor_Distribution_Poles ADD
		ConductorType int NULL
GO

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'tb_Vendor_Distribution_Poles' AND COLUMN_NAME = 'ACSR')
	ALTER TABLE tb_Vendor_Distribution_Poles ADD
		ACSR int NULL
GO

COMMIT TRANSACTION

GO


